#!/usr/bin/env python
# -*- coding: utf-8 -*-
from string import Template, strip
from tack.lib import json_app, content_headers, status_codes
from datetime import datetime
from tack_ocie.api.util import state_def, sql_string, build_row_dict


ymd_format = '%Y-%m-%d'


get_by_search_term = """
SELECT TOP 20 schedule_id,
    search_text,
    start_time
FROM ocie_schedule_search
WHERE search_text LIKE $search_term
ORDER BY start_time DESC
"""


get_on_date = """
SELECT ocie_schedule.schedule_id,
    parent_id
FROM ocie_schedule
WHERE CONVERT(CHAR(10),start,121) = $working_date 
    AND state_finish IS NULL AND state_key = $resourced_state
ORDER BY start
"""


get_on_id = """
SELECT ocie_schedule.schedule_id,
    parent_id,
    start,
    finish,
    comments,
    poc_phone,
    poc_email,
    ocie_appointment_type.css_class AS css_class,
    ocie_unit.description AS unit,
    ocie_appointment_type.description AS type
FROM ocie_schedule
LEFT OUTER JOIN ocie_appointment_type
    ON ocie_schedule.appointment_type_id 
      = ocie_appointment_type.appointment_type_id
LEFT OUTER JOIN ocie_unit
    ON ocie_schedule.unit_id 
      = ocie_unit.unit_id
WHERE (schedule_id = $schedule_id AND state_finish IS NULL)
    OR (parent_id = $schedule_id AND state_finish IS NULL)
"""


get_attendees = """
SELECT ocie_schedule_attendee.last_name,
    ocie_schedule_attendee.first_name,
    ocie_schedule_attendee.ssn,
    ocie_schedule_attendee.rank,
    ranks.abbreviation AS rank_abbreviation
FROM ocie_schedule_attendee
LEFT OUTER JOIN ranks
    ON ocie_schedule_attendee.rank = ranks.id
WHERE schedule_id = $schedule_id
ORDER BY schedule_attendee_id
"""


def get(environ, start_response):
    cursor = environ['connection'].cursor()
    search_term = strip(environ['form']['search_term'])
    output = []
    try:
        try:
            throwaway = datetime.strptime(search_term,'%d %b %Y')
            search_term = throwaway.strftime(ymd_format) 
        except:
            throwaway = datetime.strptime(search_term,ymd_format)
            search_term = throwaway.strftime(ymd_format) 
        resourced_state = state_def.RESOURCED
        working_date = sql_string(search_term)
        sql = Template(get_on_date).substitute(locals())
        cursor.execute(sql)
        results = cursor.fetchall()
        result_cd = cursor.description
        for result in results:
            result_dict = build_row_dict(result_cd, result)
            output.append(get_by_id(cursor,result_dict['schedule_id']))
    except:
        search_term = sql_string(search_term + '%')
        sql = Template(get_by_search_term).substitute(locals())
        cursor.execute(sql)
        results = cursor.fetchall()
        result_cd = cursor.description
        for result in results:
            result_dict = build_row_dict(result_cd, result)
            output.append(get_by_id(cursor,result_dict['schedule_id']))
    return json_app(output)(environ, start_response)


def get_by_id(cursor,schedule_id):
    sql = Template(get_on_id).substitute(locals())
    cursor.execute(sql)
    rows = cursor.fetchall()
    row_cd = cursor.description
    output = build_row_dict(row_cd, rows[0])
    if output['parent_id']:
        output['schedule_id'] = output['parent_id']
    del output['parent_id']
    schedule_id = output['schedule_id']
    sql = Template(get_attendees).substitute(locals())
    cursor.execute(sql)
    attendee_rows = cursor.fetchall()
    attendee_list = []
    attendee_cd = cursor.description
    for attendee in attendee_rows:
        attendee_dict = build_row_dict(attendee_cd, attendee)
        attendee_list.append(attendee_dict)
    output['attendees'] = attendee_list
    return output
